﻿using PSI.Models.TMS;
using PSI.Helper;

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Web;

namespace PSI.Services.TMS {
    public class DispatcherService : PSIBaseService {
        public List<Task> GetListData(NameValueCollection formData) {
            var dispatcher = formData["dispatcher"];
            var truckCompany = formData["truckCompany"];
            var truckNumber = formData["truckNumber"];
            var driver = formData["driver"];
            var planDT1 = formData["planDT1"];
            var planDT2 = formData["planDT2"];

            var colList = new List<string> { 
                "a.f_trip_number", "a.f_driver", "a.f_truck_num", "a.f_truck_company",
                "a.f_address", "a.f_dt_1_plan", "a.f_dt_1_driver", "a.f_dt_4_plan", "a.f_dt_4_driver",
                "a.f_status","b.f_address", "b.f_dt_1_plan", "b.f_dt_1_driver", "b.f_dt_4_plan", "b.f_dt_4_driver",
                "b.f_status", "a.f_truck_dt", "b.f_truck_dt"
            };
            var indexTripNumber = colList.IndexOf("a.f_trip_number");
            var indexDriver = colList.IndexOf("a.f_driver");
            var indexTruckNumber = colList.IndexOf("a.f_truck_num");
            var indexTruckCompany = colList.IndexOf("a.f_truck_company");
            var indexInAddress = colList.IndexOf("a.f_address");
            var indexInDT1Plan = colList.IndexOf("a.f_dt_1_plan");
            var indexInDT1Driver = colList.IndexOf("a.f_dt_1_driver");
            var indexInDT4Plan = colList.IndexOf("a.f_dt_4_plan");
            var indexInDT4Driver = colList.IndexOf("a.f_dt_4_driver");
            var indexInStatus = colList.IndexOf("a.f_status");
            var indexOutAddress = colList.IndexOf("b.f_address");
            var indexOutDT1Plan = colList.IndexOf("b.f_dt_1_driver");
            var indexOutDT1Driver = colList.IndexOf("b.f_dt_1_driver");
            var indexOutDT4Plan = colList.IndexOf("b.f_dt_4_plan");
            var indexOutDT4Driver = colList.IndexOf("b.f_dt_4_driver");
            var indexOutStatus = colList.IndexOf("b.f_status");
            var indexInTruckDT = colList.IndexOf("a.f_truck_dt");
            var indexOutTruckDT = colList.IndexOf("b.f_truck_dt");

            var sb = new StringBuilder();
            sb.Append("select ").Append(colList.CombineWithComma());
            sb.Append(" from tms_in_consignation_detail a ");
            sb.Append(" left join tms_out_consignation_detail b ");
            sb.Append(" on (a.f_trip_number = b.f_trip_number) ");
            sb.Append(" where (a.f_trip_number is not null) ");
            var p = CreateDbParameters();
            if (!string.IsNullOrEmpty(dispatcher)) {
                sb.Append(" and (a.f_dispatcher = @dispatcher)");
                p.AddWithValue("dispatcher", dispatcher);
            }
            if (!string.IsNullOrEmpty(truckCompany)) {
                sb.Append(" and (a.f_truck_company = @truckCompany)");
                p.AddWithValue("truckCompany", truckCompany);
            }
            if (!string.IsNullOrEmpty(truckNumber)) {
                sb.Append(" and (a.f_truck_num = @truckNumber)");
                p.AddWithValue("truckNumber", truckNumber);
            }
            if (!string.IsNullOrEmpty(driver)) {
                sb.Append(" and (a.f_driver = @driver)");
                p.AddWithValue("driver", driver);
            }
            if (!string.IsNullOrEmpty(planDT1) || !string.IsNullOrEmpty(planDT2)) {
                if (string.IsNullOrEmpty(planDT1)) {
                    planDT1 = planDT2;
                }
                if (string.IsNullOrEmpty(planDT2)) {
                    planDT2 = planDT1;
                }
                sb.Append(" and (convert(date, a.f_truck_dt) between @planDT1 and @planDT2)");
                p.AddWithValue("planDT1", planDT1);
                p.AddWithValue("planDT2", planDT2);
            }
            sb.Append(" order by 1 desc");

            var result = new List<Task>();
            var sql = sb.ToString();

            Query(sql, p, reader => {
                var task = new Task {
                    TripNumber = reader.GetString(indexTripNumber),
                    TruckNumber = reader.GetString(indexTruckNumber)
                        + "/" + reader.GetString(indexDriver) + "/" + reader.GetString(indexTruckCompany),
                    InStatus = reader.GetString(indexInStatus),
                    OutStatus = reader.GetString(indexOutStatus),
                    InAddress = reader.GetString(indexInAddress),
                    OutAddress=reader.GetString(indexOutAddress),
                };
                if (!reader.IsDBNull(indexInDT1Plan)) {
                    task.InDT1Plan = reader.GetDateTime(indexInDT1Plan).ToYmdHm();
                }
                if (!reader.IsDBNull(indexInDT1Driver)) {
                    task.InDT1Driver = reader.GetDateTime(indexInDT1Driver).ToYmdHm();
                }
                if (!reader.IsDBNull(indexInDT4Plan)) {
                    task.InDT4Plan = reader.GetDateTime(indexInDT4Plan).ToYmdHm();
                }
                if (!reader.IsDBNull(indexInDT4Driver)) {
                    task.InDT4Driver = reader.GetDateTime(indexInDT4Driver).ToYmdHm();
                }

                if (!reader.IsDBNull(indexOutDT1Plan)) {
                    task.OutDT1Plan = reader.GetDateTime(indexOutDT1Plan).ToYmdHm();
                }
                if (!reader.IsDBNull(indexOutDT1Driver)) {
                    task.OutDT1Driver = reader.GetDateTime(indexOutDT1Driver).ToYmdHm();
                }
                if (!reader.IsDBNull(indexOutDT4Plan)) {
                    task.OutDT4Plan = reader.GetDateTime(indexOutDT4Plan).ToYmdHm();
                }
                if (!reader.IsDBNull(indexOutDT4Driver)) {
                    task.OutDT4Driver = reader.GetDateTime(indexOutDT4Driver).ToYmdHm();
                }
                if (!reader.IsDBNull(indexInTruckDT)) {
                    task.InTruckDT = reader.GetDateTime(indexInTruckDT).ToYmdHm();
                }
                if (!reader.IsDBNull(indexOutTruckDT)) {
                    task.OutTruckDT = reader.GetDateTime(indexOutTruckDT).ToYmdHm();
                }
                result.Add(task);
            });

            if (result.Count > 100) {
                result = (from item in result select item).Take(100).ToList();
            }
            return result;
        }
    }
}